{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2190ee28",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            销售日期    员工工号  销售员       货号                    销售单编号  销量     销售额  \\\n",
      "0     2017-01-01  SS0079   于非  STY0004  C001S001-2017-01-01-007   1   405.5   \n",
      "1     2017-01-01  SS0079   于非  STY0005  C001S001-2017-01-01-007   1   405.5   \n",
      "2     2017-01-01  SS0052   王发  STY0006  C001S001-2017-01-01-007   1   270.1   \n",
      "3     2017-01-01  SS0052   王发  STY0007  C001S001-2017-01-01-007   1  1014.7   \n",
      "4     2017-01-01  SS0052   王发  STY0008  C001S001-2017-01-01-007   1   249.8   \n",
      "...          ...     ...  ...      ...                      ...  ..     ...   \n",
      "23031 2018-06-23  SS0122   赵里  STY1317  C001S001-2018-06-23-025   1   222.7   \n",
      "23032 2018-06-24  SS0122   赵里  STY1384  C001S001-2018-06-24-008   1   155.0   \n",
      "23033 2018-06-24  SS0122   赵里  STY1434  C001S001-2018-06-24-008   1   270.1   \n",
      "23034 2018-06-25  SS0149  完颜朵  STY0403  C001S001-2018-06-25-012   1   255.5   \n",
      "23035 2018-06-25  SS0149  完颜朵  STY1566  C001S001-2018-06-25-012   1   393.9   \n",
      "\n",
      "         会员ID  \n",
      "0      M12461  \n",
      "1      M12461  \n",
      "2      M12461  \n",
      "3      M12461  \n",
      "4      M12461  \n",
      "...       ...  \n",
      "23031  M16647  \n",
      "23032  M18500  \n",
      "23033  M18500  \n",
      "23034  M15616  \n",
      "23035  M15616  \n",
      "\n",
      "[23036 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "file_path = r\"C:\\Users\\Draw the sword\\Desktop\\商业数据分析\\《Power BI商业数据分析项目实战》\\第3篇 销售案例6 7 8 9\\第8章\\数据源.xlsx\"\n",
    "df_2= pd.read_excel(file_path, sheet_name='销售明细', engine='openpyxl')\n",
    "\n",
    "print(df_2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "698b92b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        会员ID  购买频次    总销售额\n",
      "0     M10002     1   207.0\n",
      "1     M10003     1   255.5\n",
      "2     M10004     1   186.2\n",
      "3     M10005     2   599.0\n",
      "4     M10006     1  1057.8\n",
      "...      ...   ...     ...\n",
      "7502  M19994     1   561.2\n",
      "7503  M19995     1   540.8\n",
      "7504  M19996     3   974.1\n",
      "7505  M19998     3  1751.9\n",
      "7506  M19999     2  1207.9\n",
      "\n",
      "[7507 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "F = df_2.groupby('会员ID')['销售单编号'].nunique().reset_index()\n",
    "F.columns = ['会员ID', '购买频次']\n",
    "\n",
    "M = df_2.groupby('会员ID')['销售额'].sum().reset_index()\n",
    "M.columns = ['会员ID', '总销售额']\n",
    "\n",
    "# 合并 F 和 M 结果\n",
    "RFM = pd.merge(F, M, on='会员ID', how='inner')\n",
    "\n",
    "# 显示结果\n",
    "print(RFM)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "baa8f34a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         会员ID    R  F       M  R得分  F得分  M得分  RFM    会员分组\n",
      "0      M12461  576  3  4360.8    1    2    2  122  重要保持会员\n",
      "1      M12461  143  3  4360.8    2    2    2  222  重要价值会员\n",
      "2      M11481  576  5  3299.4    1    2    2  122  重要保持会员\n",
      "3      M11481  482  5  3299.4    1    2    2  122  重要保持会员\n",
      "4      M11481  203  5  3299.4    2    2    2  222  重要价值会员\n",
      "...       ...  ... ..     ...  ...  ...  ...  ...     ...\n",
      "13979  M12307   45  1   172.7    2    1    1  211     新会员\n",
      "13980  M15373   43  1   222.7    2    1    1  211     新会员\n",
      "13981  M19136   38  1   182.1    2    1    1  211     新会员\n",
      "13982  M12785   38  1   945.0    2    1    1  211     新会员\n",
      "13983  M18500   37  1   425.1    2    1    1  211     新会员\n",
      "\n",
      "[13984 rows x 9 columns]\n"
     ]
    }
   ],
   "source": [
    "# 计算 R (最近一次购买日期)\n",
    "df_2['销售日期'] = pd.to_datetime(df_2['销售日期'])\n",
    "max_date = df_2['销售日期'].max()\n",
    "df_2['R'] = (max_date - df_2['销售日期']).dt.days\n",
    "\n",
    "# 计算 F (购买频次)\n",
    "F = df_2.groupby('会员ID')['销售单编号'].nunique().reset_index()\n",
    "F.columns = ['会员ID', 'F']\n",
    "\n",
    "# 计算 M (总销售额)\n",
    "M = df_2.groupby('会员ID')['销售额'].sum().reset_index()\n",
    "M.columns = ['会员ID', 'M']\n",
    "\n",
    "# 合并 R, F, M 结果\n",
    "RFM = pd.merge(df_2[['会员ID', 'R']].drop_duplicates(), F, on='会员ID', how='inner')\n",
    "RFM = pd.merge(RFM, M, on='会员ID', how='inner')\n",
    "\n",
    "# 计算 R, F, M 得分\n",
    "RFM['R得分'] = RFM['R'].apply(lambda x: 2 if x <= RFM['R'].mean() else 1)\n",
    "RFM['F得分'] = RFM['F'].apply(lambda x: 2 if x >= RFM['F'].mean() else 1)\n",
    "RFM['M得分'] = RFM['M'].apply(lambda x: 2 if x >= RFM['M'].mean() else 1)\n",
    "\n",
    "# 计算 RFM 组合\n",
    "RFM['RFM'] = RFM['R得分'].astype(str) + RFM['F得分'].astype(str) + RFM['M得分'].astype(str)\n",
    "def 会员分组(rfm):\n",
    "    return {\n",
    "        \"111\": \"流失会员\",\n",
    "        \"112\": \"重要挽留会员\",\n",
    "        \"121\": \"一般保持会员\",\n",
    "        \"122\": \"重要保持会员\",\n",
    "        \"211\": \"新会员\",\n",
    "        \"212\": \"重要发展会员\",\n",
    "        \"221\": \"一般价值会员\",\n",
    "        \"222\": \"重要价值会员\"\n",
    "    }.get(rfm, None)\n",
    "\n",
    "# 应用会员分组\n",
    "RFM['会员分组'] = RFM['RFM'].apply(会员分组)\n",
    "print(RFM[['会员ID', 'R', 'F', 'M', 'R得分', 'F得分', 'M得分', 'RFM', '会员分组']])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
